<?php
header("Content-type: text/html; charset=utf-8");
/*
 * mysqli
 */
$db_host = "localhost";    //数据库服务器名称
$db_user = "root";             // 连接数据库用户名
$db_psw = "";                // 连接数据库密码
$db_name = "my_db";           // 数据库名称

// 面向对象方式
$mysqli = new mysqli($db_host, $db_user, $db_psw, $db_name);

// select
$query = "select * from Persons";
$result = $mysqli->query($query);
if ($result && $result->num_rows > 0) {
    //判断结果集中行的数目是否大于0
    while ($row = $result->fetch_array()) {
        //循环输出结果集中的记录
        echo ($row[0]) . " " . $row[1] . " " . $row[2] . " " . $row[3];
        echo "<br>";
    }
}

// 使用free()方法将结果集中的内存释放
$result->free();


// UPDATE
$updateSql = "UPDATE Persons SET Age = '78' WHERE  FirstName = 'Peter' ";
$result = $mysqli->query($updateSql);


echo "<br><hr>预准备语句:<br><br>";

/*
预准备语句
绑定参数
字符种类        代表的数据类型
I               integer
D               double
S               string
B               blob
*/
$query = "insert into Persons (FirstName,LastName,Age) values (?,?,?)";
$result = $mysqli->prepare($query);
$FirstName = 'sy0807';
$LastName = 'employee7';
$age = 20;
//绑定参数
$result->bind_param('ssi', $FirstName, $LastName, $age);
$result->execute();                                     //执行预准备语句
$result->close();

// 绑定结果 方式
$query = "select * from Persons";
$result = $mysqli->prepare($query);                      //进行预准备语句查询
$result->execute();                                    //执行预准备语句
$result->bind_result($id, $FirstName, $LastName, $age);       //绑定结果
while ($result->fetch()) {
    echo $id . " " . $FirstName . " " . $LastName . " " . $age;
    echo "<br>";
}
$result->close();


// DELETE
$updateSql = "DELETE FROM Persons  WHERE  FirstName = 'sy0807' ";
$result = $mysqli->query($updateSql);


// 多个查询   每个查询语句之间要用“;”分开
echo "<br><hr>多个查询:<br><br>";
$query = "select FirstName from Persons ;";
$query .= "select Age from Persons ";
if ($mysqli->multi_query($query)) {
    // 执行多个查询
    while ($mysqli->more_results()) {
        $result = $mysqli->next_result();
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_row()) {
                echo $row[0] . "<br>";
            }
            $result->close();

        }
        echo("<br>-----------------<br>");
    }
}

// 关闭与MySQL服务器的连接
$mysqli->close();

/*
// 面向过程方式
$connection = mysqli_connect($db_host,$db_user,$db_psw,$db_name);
if ( $connection ) {
    echo "数据库连接成功";
}else {
    echo "数据库连接失败";
}
 */
